package com.chinasofti.myjob.util;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.chinasofti.myjob.dao.DBUtil;

/**
 * @description:将中国省市地区数据导入
 * @author yuenghao
 * @date: 2017年2月28日 下午4:17:54
 * @version: 1.0
 */
public class AreaUtilTest {
	public static void main(String[] args) {
		try {
			selectAndInsert(0);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public static void selectAndInsert(int parentId) throws SQLException {
		
		QueryRunner runner = new QueryRunner(DBUtil.getDataSource());
		String querySql = "SELECT `id`,`name`,`parentid` FROM `district` WHERE `parentid` = ?";
		List<Map<String, Object>> result = runner.query(querySql,new MapListHandler(),parentId);
		String insertSql = "INSERT INTO `t_region` (`id`,`region_name`,`parent_id`,`is_parent`) VALUES (?,?,?,?)";
		runner.batch(insertSql, toParams(result));
		System.out.println("parentId 为"+parentId +"的添加完毕!");
		for(Map<String, Object> line : result) {
			selectAndInsert((Integer)line.get("id"));
		}
	}
	
	public static Integer isParent(Integer id) throws SQLException {
		QueryRunner runner = new QueryRunner(DBUtil.getDataSource());
		String sql = "SELECT COUNT(*) FROM `district` WHERE `parentid` = ?";
		int result = runner.query(sql, new ScalarHandler<Long>(), id) != 0L ? 1 : 0;
		return result;
	}
	
	public static Object[][] toParams(List<Map<String, Object>> params) throws SQLException {
		Object[][] result = new Object[params.size()][4];
		int count = 0;
		for(Map<String,Object> line : params) {
			Integer id = (Integer)line.get("id");
			result[count][0] = id;
			result[count][1] = line.get("name");
			result[count][2] = line.get("parentid");
			result[count][3] = isParent(id);
			count++;
		}
		return result;
	}
}
